
/**
  *  EXEC sp_programing_release_yield_reprot_by_work_week 1, '1099', '0' 
  *  THIS PROC CALCULATES THE YIELD FOR PROGRAM RELEASES FOR PAST THREE MONTH AND 13 WORK WEEKS FROM THE CURRENT DATE. 
  *  SHIP DATE IS CONSIDERED FOR CALCULATION OF YIELD FOR WORK WEEKS AND MONTHS
  *  **** NOTE :WE ARE NOT CONSIDERING PROGRAM RELEASES FOR BLANK FG PRODUCTS IN YIELD CALCULATION(AS FOR BLANK FG PRODUCTS YIELD IS ALWAYS 100%).
  */

ALTER   PROCEDURE sp_programing_release_yield_reprot_by_work_week ( @RELEASE_TYPE VARCHAR(30), @PROCESS_STEP_IDS VARCHAR(1000), @DEFAULTPROMPT VARCHAR(20) )
AS 
BEGIN

	DECLARE @order_of_display INT
	DECLARE @tempMonth INT
	DECLARE @tempYear  INT
	DECLARE @tempCount INT

	DECLARE @tempPulledQty FLOAT
	DECLARE @tempShippedQty FLOAT
	DECLARE @yield FLOAT

	DECLARE @results TABLE ( Month_Or_WorkWeek VARCHAR(50), DieQty INT, Yield Float, ORDER_OF_DISPLAY INT )

	DECLARE @PROCESS_STEPS_TABLE TABLE( PROCESSSTEP_ID INT )
	INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID )  SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @PROCESS_STEP_IDS )


	DECLARE @no_of_months_needs_to_show_in_report INT
	DECLARE @no_of_works_weeks_needs_to_show_in_report INT
	SET @no_of_months_needs_to_show_in_report = 3		--Need to Show 3 Months Data in the Report.
	SET @no_of_works_weeks_needs_to_show_in_report = 13	--Need to Show 13 Works Weeks Data in the Report.


	--Adjusting the Month
	DECLARE @start_date DATETIME
	SET @start_date = DATEADD ( mm , -( @no_of_months_needs_to_show_in_report - 1 ) , GETDATE() ) 
		
	--Adjusting the Day
	SET @start_date = DATEADD ( dd , ( -datepart( dd, @start_date ) + 1 ) , @start_date ) 

	--Adjusting Time
	SET @start_date = DATEADD ( hh , ( -datepart( hh, @start_date ) ) , @start_date ) 
	SET @start_date = DATEADD ( mi , ( -datepart( mi, @start_date ) ) , @start_date ) 
	SET @start_date = DATEADD ( ss , ( -datepart( ss, @start_date ) ) , @start_date ) 
	SET @start_date = DATEADD ( ms , ( -datepart( ms, @start_date ) ) , @start_date ) 

	SET @tempCount = 1
	SET @order_of_display = 1
	WHILE ( @tempCount <= @no_of_months_needs_to_show_in_report ) 
	BEGIN

		SET @tempPulledQty = 0
		SET @tempShippedQty = 0
		SET @yield = 0
		
		SET @tempMonth = DATEPART( MM, @start_date )
		SET @tempYear  = DATEPART( YY, @start_date )

		SELECT @tempPulledQty = SUM( ISNULL( QTY_PULLED, 0 ) ), @tempShippedQty = SUM( ISNULL( QTY_SHIPPED, 0 ) ) FROM PROGRAM_RELEASE PR, PROG_RELEASE_LOT_TABLE PRLT, PRODUCT P 
		WHERE PR.PROG_RELEASE_ID = PRLT.PROG_RELEASE_ID AND PR.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND STATUS = 'SHIPPED' AND
		DATEPART( MM, Shipped_Date ) = @tempMonth AND DATEPART ( YY, Shipped_Date ) = @tempYear
		AND Blank_ProcessStep_ID IN ( SELECT ProcessStep_Id FROM @PROCESS_STEPS_TABLE )
		AND ( P.IS_BLANK_PRODUCT IS NULL OR P.IS_BLANK_PRODUCT = 1) 
		
		IF ( @tempShippedQty IS NULL )
		BEGIN
			SET @tempShippedQty = 0
		END 

		IF ( @tempPulledQty  IS NOT NULL )
		BEGIN
			SET @yield = ( ( @tempShippedQty / @tempPulledQty ) * 100 )	
		END 
		ELSE
		BEGIN			
			SET @tempPulledQty = 0	
			SET @yield = 0
		END

		INSERT INTO @results VALUES ( DATENAME( M, @start_date), @tempPulledQty , @yield , @order_of_display ) 


		SET @start_date = DATEADD ( MM , 1 , @start_date ) 
		SET @tempCount = @tempCount + 1
		SET @order_of_display = @order_of_display + 1

	END


	--Work Week Calculation.
	--SET @last_work_week =  DATEPART (WW, GETDATE())
	--PRINT @last_work_week

	DECLARE @temp_work_week_start_date DATETIME
	DECLARE @temp_work_week_end_date DATETIME
	DECLARE @diffDays INT
	DECLARE @temp_hours INT 
	DECLARE @temp_work_week INT
	DECLARE @cur_work_week INT

	SET @start_date = GETDATE()
	SET @start_date = DATEADD ( mm , ( -datepart( MM, @start_date ) + 1 ) , GETDATE() ) 
	SET @start_date = DATEADD ( dd , ( -datepart( dd, @start_date ) + 1 ) , @start_date ) 
	SET @start_date = DATEADD ( hh , ( -datepart( hh, @start_date ) ) , @start_date ) 
	SET @start_date = DATEADD ( mi , ( -datepart( mi, @start_date ) ) , @start_date ) 
	SET @start_date = DATEADD ( ss , ( -datepart( ss, @start_date ) ) , @start_date ) 
	SET @start_date = DATEADD ( ms , ( -datepart( ms, @start_date ) ) , @start_date ) 

	WHILE ( DATENAME ( weekday , @start_date ) <> 'Monday' )
	BEGIN
		SET @start_date = DATEADD ( DD , 1 , @start_date )
	END

	SET @diffDays = DATEDIFF( DD, @start_date, GETDATE() )
	SET @temp_hours = DATEDIFF( HH, @start_date, GETDATE() )
	IF ( @temp_hours%24 != 0 )
	 BEGIN
		SET @diffDays = @diffDays + 1
	 END
	
	
	SET @cur_work_week = CEILING  ( CAST( @diffDays AS FLOAT ) / 7 )


	SET @tempCount = @cur_work_week -  @no_of_works_weeks_needs_to_show_in_report + 1 
	WHILE ( @tempCount <= @cur_work_week )
	BEGIN
		
		IF ( @tempCount > 0 )
		BEGIN
			SET @tempPulledQty = 0
			SET @tempShippedQty = 0
			SET @yield = 0
	
			SET @temp_work_week_start_date = DATEADD ( DD , 7 * ( @tempCount - 1 ) , @start_date ) 
			SET @temp_work_week_end_date   = DATEADD ( DD , 7 * @tempCount, @start_date ) 

			--PRINT @tempCount
			--PRINT @temp_work_week_start_date
			--PRINT @temp_work_week_end_date

			SELECT @tempPulledQty = SUM( ISNULL( QTY_PULLED, 0 ) ), @tempShippedQty = SUM( ISNULL( QTY_SHIPPED, 0 ) ) FROM PROGRAM_RELEASE PR, PROG_RELEASE_LOT_TABLE PRLT, PRODUCT P  
			WHERE PR.PROG_RELEASE_ID = PRLT.PROG_RELEASE_ID AND PR.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND STATUS = 'SHIPPED' AND
			Shipped_Date BETWEEN @temp_work_week_start_date AND @temp_work_week_end_date
			AND Blank_ProcessStep_ID IN ( SELECT ProcessStep_Id FROM @PROCESS_STEPS_TABLE )
			AND ( P.IS_BLANK_PRODUCT IS NULL OR P.IS_BLANK_PRODUCT = 1) 
	
			--PRINT @tempPulledQty
			--PRINT @tempShippedQty
	
			IF ( @tempShippedQty IS NULL )
			BEGIN
				SET @tempShippedQty = 0
			END 
	
			IF ( @tempPulledQty  IS NOT NULL )
			BEGIN
				SET @yield = ( ( @tempShippedQty / @tempPulledQty ) * 100 )	
			END 
			ELSE
			BEGIN			
				SET @tempPulledQty = 0	
				SET @yield = 0
			END
	
			INSERT INTO @results VALUES ( @tempCount, @tempPulledQty,  @yield, @order_of_display ) 
	
		END

		SET @tempCount = @tempCount + 1
		SET @order_of_display = @order_of_display + 1

	END

	SELECT * FROM @results

END


